﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ConcordWinManager
{
    public class Doverennost
    {
        public int id_doverennost;

        public int id_consultant;

        public string Num_doverennost;

        public DateTime DateBegin;

        public DateTime DateEnd;

        public string DoverennostText;

        public void Insert()
        {
            string query = string.Format("INSERT INTO dbo.Doverennost " +
                         " ([Num_doverennost], [Date_begin], [Date_end], id_consultant) " +
                         " VALUES ('{0}','{1}', '{2}', {3})",
                         this.Num_doverennost,
                         string.Format("{2}-{1}-{0}",
                            this.DateBegin.Day.ToString(),
                            this.DateBegin.Month.ToString(),
                            this.DateBegin.Year.ToString()),
                         string.Format("{2}-{1}-{0}",
                            this.DateEnd.Day.ToString(),
                            this.DateEnd.Month.ToString(),
                            this.DateEnd.Year.ToString()),
                         this.id_consultant
                         );

            DB.execute_sql(query);
            this.id_doverennost = DB.max_num("[dbo].[Doverennost]", "id_doverennost");
        }

        public bool SelectByConsultantId(int id)
        {
            string query = string.Format("SELECT * FROM [dbo].[Doverennost] WHERE id_consultant = '{0}'",
              id);

            DataSet ds = new DataSet();

            DB.query_table(query, "dbo.Doverennost", ref ds);

            if (ds.Tables[0].Rows.Count == 0)
            {
                return false;
            }
            else
            {
                this.id_doverennost = int.Parse(ds.Tables["dbo.Doverennost"].Rows[0]["id_doverennost"].ToString());
                this.Num_doverennost = ds.Tables["dbo.Doverennost"].Rows[0]["Num_doverennost"].ToString();
                this.DateBegin = DateTime.Parse(ds.Tables["dbo.Doverennost"].Rows[0]["Date_begin"].ToString());
                this.DateEnd = DateTime.Parse(ds.Tables["dbo.Doverennost"].Rows[0]["Date_end"].ToString());
                if (int.TryParse(ds.Tables["dbo.Doverennost"].Rows[0]["id_consultant"].ToString(), out id_consultant))
                {
                    return true;
                }

                return false;
            }            
        }

        public string ReturnDoverennostText()
        {
            return string.Format("№{0} с {1} по {2}",
                         this.Num_doverennost,
                         string.Format("{0}.{1}.{2}",
                            this.DateBegin.Day.ToString("0#"),
                            this.DateBegin.Month.ToString("0#"),
                            this.DateBegin.Year.ToString()),
                         string.Format("{0}.{1}.{2}",
                            this.DateEnd.Day.ToString("0#"),
                            this.DateEnd.Month.ToString("0#"),
                            this.DateEnd.Year.ToString())
                            );
        }

        public void SelectById(int id)
        {
            string query = string.Format("SELECT * FROM [dbo].[Doverennost] WHERE id_doverennost = '{0}'",
                         id);

            DataSet ds = new DataSet();

            DB.query_table(query, "dbo.Doverennost", ref ds);

            if (ds.Tables[0].Rows.Count != 0)
            {
                this.id_doverennost = int.Parse(ds.Tables["dbo.Doverennost"].Rows[0]["id_doverennost"].ToString());
                this.Num_doverennost = ds.Tables["dbo.Doverennost"].Rows[0]["Num_doverennost"].ToString();
                this.DateBegin = DateTime.Parse(ds.Tables["dbo.Doverennost"].Rows[0]["Date_begin"].ToString());
                this.DateEnd = DateTime.Parse(ds.Tables["dbo.Doverennost"].Rows[0]["Date_end"].ToString());
                if (int.TryParse(ds.Tables["dbo.Doverennost"].Rows[0]["id_consultant"].ToString(), out id_consultant))
                {
                    
                }
            }
        }

        public void Update()
        {
            string query = string.Format("UPDATE [dbo].[Doverennost] SET Num_doverennost='{0}', Date_begin='{1}', Date_end='{2}', " +
               " id_consultant='{3}' WHERE (id_doverennost = '{4}')",
                    this.Num_doverennost,
                    string.Format("{2}-{1}-{0}",
                        this.DateBegin.Day.ToString(),
                        this.DateBegin.Month.ToString(),
                        this.DateBegin.Year.ToString()),
                    string.Format("{2}-{1}-{0}",
                        this.DateEnd.Day.ToString(),
                        this.DateEnd.Month.ToString(),
                        this.DateEnd.Year.ToString()),                            
                    this.id_consultant,
                    this.id_doverennost
               );

            DataSet ds = new DataSet();

            DB.query_table(query, "[dbo].[Doverennost]", ref ds);
        }


    }
}
